using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Text;
using UDS.Management.Data.Package;
using System.Data;
using System.Data.SqlTypes;

namespace UDS.Management.Data
{
    internal static class SqlHelper
    {
        /// <summary>
        /// Executes the content against the database with the given transaction and Connection.
        /// The content is broken apart into its go statements and executed.
        /// </summary>
        /// <exception cref="Exceptions.SqlScriptException">Thrown if there is an sql script error</exception>
        /// <param name="Source">The sql source to execute</param>
        /// <param name="trans">The transaction to use</param>
        /// <param name="CommandTimeOut">The time out to use for the command</param>
        internal static void ExecuteContent(SqlSource Source, SqlTransaction trans, int CommandTimeOut)
        {
            // execute script
            SqlCommandReader rdr = Source.Content;
            SqlCommand cmd;
            while ((cmd = rdr.ReadSqlCommand()) != null)
            {
                try
                {
                    using (cmd)
                    {
                        cmd.CommandTimeout = CommandTimeOut;
                        cmd.Transaction = trans;
                        cmd.Connection = trans.Connection;
                        cmd.ExecuteNonQuery();
                    }
                }
                catch (SqlException sqlEx)
                {
                    throw new Exceptions.SqlScriptException(Source.Name, rdr.BeginLineNumber, rdr.EndingLineNumber, sqlEx.Message, sqlEx);
                }
            }
        }

        /// <summary>
        /// Executes the content against the database with the given transaction and Connection.
        /// The content is broken apart into its go statements and executed.
        /// </summary>
        /// <exception cref="Exceptions.SqlScriptException">Thrown if there is an sql script error</exception>
        /// <param name="Source">The sql source to execute</param>
        /// <param name="connection">The connection to use</param>
        /// <param name="CommandTimeOut">The time out to use for the command</param>
        internal static void ExecuteContent(SqlSource Source, SqlConnection connection, int CommandTimeOut)
        {
            // execute script
            SqlCommandReader rdr = Source.Content;
            SqlCommand cmd;
            while ((cmd = rdr.ReadSqlCommand()) != null)
            {
                try
                {
                    using (cmd)
                    {
                        cmd.CommandTimeout = CommandTimeOut;
                        cmd.Connection = connection;
                        cmd.ExecuteNonQuery();
                    }
                }
                catch (SqlException sqlEx)
                {
                    throw new Exceptions.SqlScriptException(Source.Name, rdr.BeginLineNumber, rdr.EndingLineNumber, sqlEx.Message, sqlEx);
                }
            }
        }

        /// <summary>
        /// Executes the content against the database with the given transaction and Connection.
        /// </summary>
        internal static void ExecuteContent(string name, string sql, SqlTransaction trans, int CommandTimeOut)
        {
            if (string.IsNullOrEmpty(sql))
            {
                throw new ArgumentNullException("sql");
            }

            using (var sr = new System.IO.StringReader(sql))
            {
                var source = new SqlSource(name, sr);
                ExecuteContent(source, trans, CommandTimeOut);
            }
        }

        /// <summary>
        /// Executes the content against the database with the given transaction and Connection.
        /// </summary>
        internal static void ExecuteContent(string name, string sql, SqlConnection connection, int CommandTimeOut)
        {
            if (string.IsNullOrEmpty(sql))
            {
                throw new ArgumentNullException("sql");
            }

            using (var sr = new System.IO.StringReader(sql))
            {
                var source = new SqlSource(name, sr);
                ExecuteContent(source, connection, CommandTimeOut);
            }
        }

        /// <summary>
        /// Get the current time in the database.
        /// </summary>
        /// <param name="connection">The connection to use</param>
        internal static DateTime GetCurrentDBTime(SqlConnection connection)
        {
            using (SqlCommand cmd = connection.CreateCommand())
            {
                cmd.CommandText = "SELECT getdate();";
                cmd.CommandType = System.Data.CommandType.Text;

                using (var dr = cmd.ExecuteReader())
                {
                    dr.Read();
                    return dr.GetDateTime(0);
                }
            }
        }

        /// <summary>
        /// Get all stored procedures which was created before the given time.
        /// </summary>
        internal static string[] GetStoredProcedures(SqlTransaction trans, DateTime maxCreateDate)
        {
            using (var cmd = trans.Connection.CreateCommand())
            {
                cmd.Transaction = trans;
                cmd.CommandType = CommandType.Text;

                if (maxCreateDate != DateTime.MaxValue)
                {
                    cmd.CommandText = "SELECT [name] FROM sys.procedures WHERE ([create_date] < @max_date) AND ([name] LIKE 'sproc_%') ORDER BY [name] asc"; ;

                    var parameter = new SqlParameter("@max_date", SqlDbType.DateTime);
                    parameter.Value = (maxCreateDate > SqlDateTime.MaxValue.Value) ? SqlDateTime.MaxValue.Value : maxCreateDate;
                    cmd.Parameters.Add(parameter);
                }
                else
                {
                    cmd.CommandText = "SELECT [name] FROM sys.procedures WHERE [name] LIKE 'sproc_%' ORDER BY [name] asc";
                }

                using (var dr = cmd.ExecuteReader())
                {
                    List<string> list = new List<string>();
                    while (dr.Read())
                    {
                        list.Add(dr.GetString(0));
                    }

                    return list.ToArray();
                }
            }
        }

        internal static void DropStoredProcedure(SqlTransaction trans, string spName, int CommandTimeOut)
        {
            VerifyIdentifierName(spName, true);

            const string sqlFormat = @"IF EXISTS (SELECT 1 FROM sys.objects WHERE [object_id] = OBJECT_ID(N'[dbo].[{0}]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[{0}];";
            string sql = string.Format(sqlFormat, spName);
            ExecuteContent("DROP " + spName, sql, trans, CommandTimeOut);
        }

        /// <summary>
        /// Checks if the specified name is valid for a SQL identifier.
        /// </summary>
        internal static bool VerifyIdentifierName(string name, bool throwException)
        {
            if (string.IsNullOrEmpty(name))
            {
                // Cannot be empty.
                goto INVALID_IDENTIFIER;
            }

            char firstChar = name[0];
            if (firstChar != '_' && !('a' <= firstChar && firstChar <= 'z') && !('A' <= firstChar && firstChar <= 'Z'))
            {
                // The first character must be: a-z, A-Z, _
                goto INVALID_IDENTIFIER;
            }

            for (int i = 1; i < name.Length; i++)
            {
                char ch = name[i];
                if (ch == '_')
                {
                    continue;
                }

                if ('0' <= ch && ch <= '9')
                {
                    continue;
                }

                if ('a' <= ch && ch <= 'z')
                {
                    continue;
                }

                if ('A' <= ch && ch <= 'Z')
                {
                    continue;
                }

                goto INVALID_IDENTIFIER;
            }

            return true;

        INVALID_IDENTIFIER:
            if (throwException)
            {
                throw new ArgumentException(string.Format("'{0}' is not a valid SQL identifier.", name), "name");
            }
            return false;
        }
    }
}
